package db

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"strings"
)

var Db *sql.DB
var err error

// RetainSchema 系统数据库 默认不应该返回
var RetainSchema = "sys,mysql,information_schema,performance_schema"

// LoadSchemaList 加载数据名称
// driverName 驱动名称 默认mysql
// userName 用户名 例如root
// password 密码 例如root
// host 链接地址 例如 127.0.0.1：3306
func LoadSchemaList(driverName, userName, password, host string) []string {
	if driverName == "" {
		driverName = "mysql"
	}
	Db, err = sql.Open(driverName, fmt.Sprintf("%s:%s@(%s)/", userName, password, host))
	if err != nil {
		fmt.Println(fmt.Errorf("加载数据库驱动异常：%s", err))
		return nil
	}
	//关闭链接
	defer func() { _ = Db.Close() }()
	var rows *sql.Rows
	rows, err = Db.Query("SELECT SCHEMA_NAME FROM information_schema.SCHEMATA")
	if err != nil {
		fmt.Println(fmt.Errorf("查询数据库异常：%s", err))
		return nil
	}
	defer func() {
		_ = rows.Close()
	}()
	var SchemaList []string
	var SchemaName string
	for rows.Next() {
		err = rows.Scan(&SchemaName)
		if err != nil {
			fmt.Println(fmt.Errorf("遍历数据库名称异常：%s", err))
			return nil
		}
		//过滤掉系统数据库
		if strings.Index(RetainSchema, SchemaName) < 0 {
			SchemaList = append(SchemaList, SchemaName)
		}
	}
	return SchemaList
}

// LoadTableList 加载数据名称
// driverName 驱动名称 默认mysql
// userName 用户名 例如root
// password 密码 例如root
// host 链接地址 例如 127.0.0.1：3306
// schemaName 数据库名称 例如 mysql
func LoadTableList(driverName, userName, password, host, schemaName string) []string {
	if driverName == "" {
		driverName = "mysql"
	}
	Db, err = sql.Open(driverName, fmt.Sprintf("%s:%s@(%s)/", userName, password, host))
	if err != nil {
		fmt.Println(fmt.Errorf("加载数据库驱动异常：%s", err))
		return nil
	}
	//关闭链接
	defer func() { _ = Db.Close() }()
	var rows *sql.Rows
	rows, err = Db.Query(fmt.Sprintf("SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA='%s'", schemaName))
	if err != nil {
		fmt.Println(fmt.Errorf("查询数据库表格异常：%s", err))
	}
	defer func() {
		_ = rows.Close()
	}()
	var TableList []string
	var TableName string
	for rows.Next() {
		err = rows.Scan(&TableName)
		if err != nil {
			fmt.Println(fmt.Errorf("遍历数据库表名称异常：%s", err))
			return nil
		}
		TableList = append(TableList, TableName)
	}
	return TableList
}

// ColumnBean 字段属性
type ColumnBean struct {
	ColumnName string
	DataType   string
	ColumnLen  int64
}

// LoadColumnList 加载数据库表字段名称
// driverName 驱动名称 默认mysql
// userName 用户名 例如root
// password 密码 例如root
// host 链接地址 例如 127.0.0.1：3306
// schemaName 数据库名称 例如 test
// tableName 数据库表名称 例如 test
func LoadColumnList(driverName, userName, password, host, schemaName, tableName string) []ColumnBean {
	if driverName == "" {
		driverName = "mysql"
	}
	Db, err = sql.Open(driverName, fmt.Sprintf("%s:%s@(%s)/%s", userName, password, host, schemaName))
	if err != nil {
		fmt.Println(fmt.Errorf("加载数据库驱动异常：%s", err))
		return nil
	}
	//关闭链接
	defer func() { _ = Db.Close() }()
	var rows *sql.Rows
	rows, err = Db.Query(fmt.Sprintf("SELECT COLUMN_NAME AS 'ColumnName',DATA_TYPE AS 'DataType',CHARACTER_MAXIMUM_LENGTH AS 'ColumnLen'"+
		" FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA='%s' AND TABLE_NAME='%s'", schemaName, tableName))
	if err != nil {
		fmt.Println(fmt.Errorf("查询数据库表格异常：%s", err))
		return nil
	}
	defer func() {
		_ = rows.Close()
	}()
	var TableList []ColumnBean
	var ColumnName string
	var DataType string
	var ColumnLen sql.NullInt64
	for rows.Next() {
		err = rows.Scan(&ColumnName, &DataType, &ColumnLen)
		if err != nil {
			fmt.Println(fmt.Errorf("遍历数据库表字段异常：%s", err))
			return nil
		}
		row := &ColumnBean{ColumnName, DataType, ColumnLen.Int64}
		TableList = append(TableList, *row)
	}
	return TableList
}

func Open(driverName, userName, password, host, schemaName string) {
	Db, err = sql.Open(driverName, fmt.Sprintf("%s:%s@(%s)/%s", userName, password, host, schemaName))
	if err != nil {
		fmt.Println(fmt.Errorf("加载数据库驱动异常：%s", err))
		return
	}
}
